package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class BalanceSheets extends SvrProcess {
	
	/** Current context	*/
	private Properties m_ctx;
	/**	 */
	private ProcessInfo pi;
	/** Account Sign					*/
	private String				AccountSign = "N";
	/** */
	private Timestamp			DateAcct_From = null;
	private Timestamp			DateAcct_To = null;
	/**	Client Parameter					*/
	private int					AD_Client_ID = 0;
	/**	Org Parameter					*/
	private int					AD_Org_ID = 0;
	/**	Account Parameter				*/
	private int					Account_ID = 0;
	/**	BPartner Parameter				*/
	private int					C_BPartner_ID = 0;
	/** User Element 1 Parameter		*/
	private int					UserElement1_ID = 0;
	/**	Parameter Where Clause			*/
	//private StringBuffer		m_parameterWhere = new StringBuffer();
	/** */
	private static CLogger	s_log	= CLogger.getCLogger (BalanceSheets.class);
	/** */
	private String path = "";

	/**
	 *  Prepare - e.g., get Parameters.
	 */
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)
		{
			String name = para[i].getParameterName();
			if (para[i].getParameter() == null);
			else if (name.equals("AD_Client_ID"))
				AD_Client_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("AD_Org_ID"))
				AD_Org_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("Account_ID"))
				Account_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("DateAcct_From"))
				DateAcct_From = (Timestamp)para[i].getParameter();
			else if (name.equals("DateAcct_To"))
				DateAcct_To = (Timestamp)para[i].getParameter();
			else if (name.equals("C_BPartner_ID"))
				C_BPartner_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("UserElement1_ID"))
				UserElement1_ID = ((BigDecimal)para[i].getParameter()).intValue();
			else if (name.equals("AccountSign"))
				AccountSign = (String)para[i].getParameter();
			else
				log.log(Level.SEVERE, "Unknown Parameter: " + name);
		}
		path = Util.localFilePath;
		/// here another code)
	}
	@Override
	protected String doIt() throws Exception {
		// Types of cells for
		Label label;
		Number number;
		// There are checks for a specified pattern
		String templateName = "tempOSV";
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if(entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate")+" "+templateName);
		
		// We define a path to generate
		String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
		StringBuffer fullPath = new StringBuffer(path); 
		fullPath.append("Temporary Balance Sheets");
		fullPath.append(fileExtension);	
		// 
        File templateCopy = new File(fullPath.toString());
        File temporaryFile = entry.getFile(Util.localFilePath+templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		 try {
			 tableBook = Workbook.getWorkbook(temporaryFile);
			 copy = Workbook.createWorkbook(templateCopy, tableBook);
			 sheet = copy.getSheet(0);
        } catch (Exception e) {
        	ADialog.error(999, null, "Error tableBook. " + e.getMessage());
        	return "Error tableBook.";
        }

       ExcelCell cellStart =  Util.getCellStart(tableBook,">>");
       // Style for value column
       WritableCellFormat valuestyle = new WritableCellFormat();
       valuestyle.setBorder(Border.LEFT, BorderLineStyle.MEDIUM, Colour.BLACK);
       valuestyle.setBorder(Border.RIGHT, BorderLineStyle.THIN, Colour.BLACK);
       valuestyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
       valuestyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
       valuestyle.setAlignment(Alignment.LEFT);
       valuestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
       valuestyle.setWrap(true);
      // Style for name column
       WritableCellFormat namestyle = new WritableCellFormat();
       namestyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
       namestyle.setAlignment(Alignment.LEFT);
       namestyle.setVerticalAlignment(VerticalAlignment.CENTRE);
       namestyle.setWrap(true);
       //Style for number column
       WritableCellFormat numcolstyle = new WritableCellFormat();
       numcolstyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
       numcolstyle.setAlignment(Alignment.RIGHT);
       numcolstyle.setVerticalAlignment(VerticalAlignment.CENTRE);
       numcolstyle.setWrap(true);
       //Style for last number column
       WritableCellFormat lastNumcolstyle = new WritableCellFormat();
       lastNumcolstyle.setBorder(Border.RIGHT, BorderLineStyle.MEDIUM, Colour.BLACK);
       lastNumcolstyle.setBorder(Border.LEFT, BorderLineStyle.THIN, Colour.BLACK);
       lastNumcolstyle.setBorder(Border.TOP, BorderLineStyle.THIN, Colour.BLACK);
       lastNumcolstyle.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.BLACK);
       lastNumcolstyle.setAlignment(Alignment.RIGHT);
       lastNumcolstyle.setVerticalAlignment(VerticalAlignment.CENTRE);
       lastNumcolstyle.setWrap(true);
      //Style for total row
       WritableCellFormat totalrowstyle = new WritableCellFormat();
       totalrowstyle.setBorder(Border.ALL, BorderLineStyle.MEDIUM, Colour.BLACK);
       totalrowstyle.setAlignment(Alignment.RIGHT);
       totalrowstyle.setVerticalAlignment(VerticalAlignment.CENTRE);
       totalrowstyle.setWrap(true);
      // 
       int col = cellStart.getC(); 
       int row = cellStart.getR();
      
       AccountValue[] lines =  getAccountValue();
       AccountValue[] bplines = null;
       AccountValue[] uslines = null;
       label = new Label(1, 3, lines[0].getPeriod());
       sheet.addCell(label);
       int n = 0;
       double sumDr1 = 0;
       double sumCr1 = 0;
       double sumDr2 = 0;
       double sumCr2 = 0;
       double sumDr3 = 0;
       double sumCr3 = 0;
       int rowcount=0;
       for(int i=row; n<lines.length;i++){ 
    	   label = new Label(col, i, lines[n].getValue(), valuestyle);        
    	   sheet.addCell(label);
    	   label = new Label(col+1, i, lines[n].getName(), namestyle);        
    	   sheet.addCell(label);   	   
    	   number = new Number(col+2, i, lines[n].getDr1(), numcolstyle);     
    	   sheet.addCell(number);    	   
    	   number = new Number(col+3, i, lines[n].getCr1(), numcolstyle);     
    	   sheet.addCell(number);   	   
    	   number = new Number(col+4, i, lines[n].getDr2(), numcolstyle);  	  
    	   sheet.addCell(number);    	   
    	   number = new Number(col+5, i, lines[n].getCr2(), numcolstyle);     
    	   sheet.addCell(number);   	   
    	   number = new Number(col+6, i, lines[n].getDr3(), numcolstyle);     
    	   sheet.addCell(number);    	   
    	   number = new Number(col+7, i, lines[n].getCr3(), lastNumcolstyle); 
    	   sheet.addCell(number);
    	   
    	   bplines = getBPartnerValue(lines[n].getAccount_id());
    	   int m = 0;
    	   for(int j=i+1; m < bplines.length;j++){
    		    
    		   label = new Label(col, j, bplines[m].getValue(), valuestyle);    		  
    		   sheet.addCell(label);    		   
    		   label = new Label(col+1, j, bplines[m].getName(), namestyle);    		   
    		   sheet.addCell(label);   		   
    		   number = new Number(col+2, j, bplines[m].getDr1(), numcolstyle);    		  
    		   sheet.addCell(number);   		   
    		   number = new Number(col+3, j, bplines[m].getCr1(), numcolstyle);    		  
    		   sheet.addCell(number);   		   
    		   number = new Number(col+4, j, bplines[m].getDr2(), numcolstyle);    		  
    		   sheet.addCell(number); 		   
    		   number = new Number(col+5, j, bplines[m].getCr2(), numcolstyle);    		  
    		   sheet.addCell(number);   		   
    		   number = new Number(col+6, j, bplines[m].getDr3(), numcolstyle);    		   
    		   sheet.addCell(number);   		   
    		   number = new Number(col+7, j, bplines[m].getCr3(), lastNumcolstyle);    	  
    		   sheet.addCell(number);
    		   	
    		   uslines = getUserelemen1Value(lines[n].getAccount_id(), bplines[m].getC_bpartner_id()); 
    		   int k = 0;
    		   for(int p=j+1; k < uslines.length;p++){	  
    			   
    			   label = new Label(col, p, uslines[k].getCms_contract_contractnum(), valuestyle); 
    			   sheet.addCell(label);   			   
    			   label = new Label(col+1, p, uslines[k].getDescription(), namestyle);    			
    			   sheet.addCell(label);   			   
    			   number = new Number(col+2, p, uslines[k].getDr1(), numcolstyle);    			   
    			   sheet.addCell(number);		   
    			   number = new Number(col+3, p, uslines[k].getCr1(), numcolstyle);    			    
    			   sheet.addCell(number);  			   
    			   number = new Number(col+4, p, uslines[k].getDr2(), numcolstyle);    			    
    			   sheet.addCell(number);    			   
    			   number = new Number(col+5, p, uslines[k].getCr2(), numcolstyle);    			   
    			   sheet.addCell(number);   			   
    			   number = new Number(col+6, p, uslines[k].getDr3(), numcolstyle);    			   
    			   sheet.addCell(number);   			   
    			   number = new Number(col+7, p, uslines[k].getCr3(), lastNumcolstyle);    		   
    			   sheet.addCell(number);
    			   i++;j++; k++;
    		   }
    		  i++;m++;
    	   }
    	   sumDr1+=lines[n].getDr1();sumDr2+=lines[n].getDr2();sumDr3+=lines[n].getDr3();
    	   sumCr1+=lines[n].getCr1();sumCr2+=lines[n].getCr2();sumCr3+=lines[n].getCr3();
    	   n++;rowcount=i;
       }
       //total row
       label = new Label(col, rowcount+1, "", totalrowstyle);
       sheet.addCell(label);
       label = new Label(col+1, rowcount+1, "Итого", totalrowstyle);
       sheet.addCell(label);
       number = new Number(col+2, rowcount+1, sumDr1, totalrowstyle);
       sheet.addCell(number);
       number = new Number(col+3, rowcount+1, sumCr1, totalrowstyle);
       sheet.addCell(number);
       number = new Number(col+4, rowcount+1, sumDr2, totalrowstyle);
       sheet.addCell(number);
       number = new Number(col+5, rowcount+1, sumCr2, totalrowstyle);
       sheet.addCell(number);
       number = new Number(col+6, rowcount+1, sumDr3, totalrowstyle);
       sheet.addCell(number);
       number = new Number(col+7, rowcount+1, sumCr3, totalrowstyle);
       sheet.addCell(number);
      //
       copy.write();
       copy.close();
       tableBook.close();
       temporaryFile.delete();
       
       // Open the file with the application associated
		String fileName = fullPath.toString();
		Util.launchFile(new File(fileName));
		
       return Msg.translate(m_ctx, "Success");
	}
	
	private AccountValue[] getAccountValue(){
		
		//
		AccountValue vacLine = null;
    	List<AccountValue> list = new ArrayList<AccountValue>();
    	//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;		
			
		//
			try
			{
				pstmt = DB.prepareStatement(getSql(),null);
				pstmt.setTimestamp(1, DateAcct_From);
				pstmt.setTimestamp(2, DateAcct_To);
				pstmt.setInt(3, Env.getAD_Client_ID(Env.getCtx()));
				pstmt.setInt(4, Env.getAD_Org_ID(Env.getCtx()));
				pstmt.setTimestamp(5, DateAcct_To);
				pstmt.setInt(6, Env.getAD_Client_ID(Env.getCtx()));
				pstmt.setInt(7, Env.getAD_Org_ID(Env.getCtx()));
				pstmt.setTimestamp(8, DateAcct_From);
				pstmt.setInt(9, Env.getAD_Client_ID(Env.getCtx()));
				pstmt.setInt(10, Env.getAD_Org_ID(Env.getCtx()));
				pstmt.setTimestamp(11, DateAcct_From);
				pstmt.setTimestamp(12, DateAcct_To);
				pstmt.setInt(13, Env.getAD_Client_ID(Env.getCtx()));
				pstmt.setString(14, AccountSign);
				if(Account_ID !=0)
					pstmt.setInt(15, Account_ID);
				rs = pstmt.executeQuery();	
				while (rs.next ())
				{
					vacLine = new AccountValue();
					vacLine.setAd_client_id(rs.getInt(1));
					vacLine.setAccount_id(rs.getInt(2));
					vacLine.setAd_org_id(rs.getInt(3));
					vacLine.setDr1(rs.getDouble(4));
					vacLine.setCr1(rs.getDouble(5));
					vacLine.setDr2(rs.getDouble(6));
					vacLine.setCr2(rs.getDouble(7));
					vacLine.setDr3(rs.getDouble(8));
					vacLine.setCr3(rs.getDouble(9));
					vacLine.setPeriod(rs.getString(10));
					vacLine.setValue(rs.getString(11));
					vacLine.setName(rs.getString(12));
					list.add(vacLine);
					vacLine = null;
				}
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
			return list.toArray(new AccountValue[list.size()]);
	}
	
	private AccountValue[] getBPartnerValue(int account_id){
		//
		AccountValue vacLine = null;
    	List<AccountValue> list = new ArrayList<AccountValue>();
    	//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;		
			
		//
			try
			{
				pstmt = DB.prepareStatement(getBpartnerSql(),null);
				pstmt.setInt(1, Env.getAD_Client_ID(Env.getCtx()));
				pstmt.setInt(2, Env.getAD_Org_ID(Env.getCtx()));
				pstmt.setInt(3,account_id);
				pstmt.setTimestamp(4, DateAcct_To);
				pstmt.setInt(5, Env.getAD_Client_ID(Env.getCtx()));
				pstmt.setInt(6, Env.getAD_Org_ID(Env.getCtx()));
				pstmt.setInt(7,account_id);
				pstmt.setTimestamp(8, DateAcct_From);
				pstmt.setInt(9, Env.getAD_Client_ID(Env.getCtx()));
				pstmt.setInt(10, Env.getAD_Org_ID(Env.getCtx()));
				pstmt.setInt(11,account_id);
				pstmt.setTimestamp(12, DateAcct_From);
				pstmt.setTimestamp(13, DateAcct_To);
				if(C_BPartner_ID!=0)
					pstmt.setInt(14, C_BPartner_ID);
				rs = pstmt.executeQuery();	
				while (rs.next ())
				{
					vacLine = new AccountValue();
					vacLine.setC_bpartner_id(rs.getInt(1));
					vacLine.setValue(rs.getString(2));
					vacLine.setName(rs.getString(3));
					vacLine.setDr1(rs.getDouble(4));
					vacLine.setCr1(rs.getDouble(5));
					vacLine.setDr2(rs.getDouble(6));
					vacLine.setCr2(rs.getDouble(7));
					vacLine.setDr3(rs.getDouble(8));
					vacLine.setCr3(rs.getDouble(9));
					list.add(vacLine);
					vacLine = null;
				}
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
			return list.toArray(new AccountValue[list.size()]);
	}
	
	private AccountValue[] getUserelemen1Value(int account_id,int c_bpartner_id){
		//
		AccountValue vacLine = null;
    	List<AccountValue> list = new ArrayList<AccountValue>();
    	//
	    PreparedStatement pstmt = null;
		ResultSet rs = null;		
			
		//
			try
			{
				pstmt = DB.prepareStatement(getUserelementSql(),null);
				pstmt.setInt(1, Env.getAD_Client_ID(Env.getCtx()));
				pstmt.setInt(2, Env.getAD_Org_ID(Env.getCtx()));
				pstmt.setInt(3,c_bpartner_id);
				pstmt.setTimestamp(4, DateAcct_To);
				pstmt.setInt(5,account_id);
				pstmt.setInt(6, Env.getAD_Client_ID(Env.getCtx()));
				pstmt.setInt(7, Env.getAD_Org_ID(Env.getCtx()));
				pstmt.setInt(8,c_bpartner_id);
				pstmt.setTimestamp(9, DateAcct_From);
				pstmt.setInt(10,account_id);
				pstmt.setInt(11, Env.getAD_Client_ID(Env.getCtx()));
				pstmt.setInt(12, Env.getAD_Org_ID(Env.getCtx()));
				pstmt.setInt(13,c_bpartner_id);
				pstmt.setTimestamp(14, DateAcct_From);
				pstmt.setTimestamp(15, DateAcct_To);
				pstmt.setInt(16,account_id);
				if(UserElement1_ID!=0)
					pstmt.setInt(17, UserElement1_ID);
				rs = pstmt.executeQuery();	
				while (rs.next ())
				{
					vacLine = new AccountValue();
					vacLine.setUserelement1_id(rs.getInt(1));
					vacLine.setDescription(rs.getString(2));
					vacLine.setCms_contract_contractnum(rs.getString(3));
					vacLine.setDr1(rs.getDouble(4));
					vacLine.setCr1(rs.getDouble(5));
					vacLine.setDr2(rs.getDouble(6));
					vacLine.setCr2(rs.getDouble(7));
					vacLine.setDr3(rs.getDouble(8));
					vacLine.setCr3(rs.getDouble(9));
					list.add(vacLine);
					vacLine = null;
				}
			}
			catch (SQLException e)
			{
				s_log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}	
			return list.toArray(new AccountValue[list.size()]);
	}
	
	private String getSql(){
		StringBuffer sql = new StringBuffer();
		sql.append("select vv.*,c.value,c.name from adempiere.c_elementvalue c "
				+" inner join "
				+" (select ww.ad_client_id,ww.account_id,ww.ad_org_id, " 
				+" (CASE WHEN (COALESCE(sum(xx.dr1),0)-COALESCE(sum(xx.cr1),0))<0 THEN 0 ELSE (COALESCE(sum(xx.dr1),0)-COALESCE(sum(xx.cr1),0)) END)as dr1,"
				+" (CASE WHEN (COALESCE(sum(xx.dr1),0)-COALESCE(sum(xx.cr1),0))<0 THEN -1*(COALESCE(sum(xx.dr1),0)-COALESCE(sum(xx.cr1),0)) ELSE 0 END)as cr1,"
				+" COALESCE(sum(xx.dr2),0)as dr2,"
				+" COALESCE(sum(xx.cr2),0)as cr2,"
				+" (CASE WHEN((COALESCE(sum(xx.dr1),0)+COALESCE(sum(xx.dr2),0))-(COALESCE(sum(xx.cr1),0)+COALESCE(sum(xx.cr2),0)))<0"
				+" THEN 0 ELSE ((COALESCE(sum(xx.dr1),0)+COALESCE(sum(xx.dr2),0))-(COALESCE(sum(xx.cr1),0)+COALESCE(sum(xx.cr2),0)))END)as dr3,"
				+" (CASE WHEN((COALESCE(sum(xx.dr1),0)+COALESCE(sum(xx.dr2),0))-(COALESCE(sum(xx.cr1),0)+COALESCE(sum(xx.cr2),0)))<0"
				+" THEN -1*((COALESCE(sum(xx.dr1),0)+COALESCE(sum(xx.dr2),0))-(COALESCE(sum(xx.cr1),0)+COALESCE(sum(xx.cr2),0))) ELSE 0 END)as cr3,"
				+" adempiere.erps_periodstring(?,?) as period " //1,2
				+" from"
				+" ( select account_id,ad_client_id,ad_org_id from adempiere.fact_acct where ad_client_id = ? and ad_org_id = ? and dateacct<=?"    ////3,4,5
				+" group by account_id,ad_client_id,ad_org_id ) as ww"
				+" left join "
				+" ( select * from    "
				+" (select account_id as acct1, ad_org_id, sum(amtacctdr) as dr1, sum(amtacctcr) as cr1 "
				+" from adempiere.fact_acct where ad_client_id = ? and ad_org_id=? and dateacct<? group by account_id, ad_org_id"      ////6,7,8
				+" ) as xx"
				+" full join"
				+" ( select account_id as acct2, ad_org_id, sum(amtacctdr) as dr2, sum(amtacctcr) as cr2" 
				+" from adempiere.fact_acct where ad_client_id = ? and ad_org_id=? and dateacct between ? and ? group by account_id, ad_org_id"  ///9,10,11,12
				+" ) as yy on(xx.acct1=yy.acct2)"
				+" ) as xx on(ww.account_id=xx.acct1 or ww.account_id=xx.acct2)"
				+" group by ww.ad_client_id,ww.account_id,ww.ad_org_id"
				+" ) as vv on(c.c_elementvalue_id=vv.account_id)"
				+" where c.ad_client_id=? and c.accountsign=? and (dr1!=0 or cr1!=0 or dr2!=0 or cr2!=0)"); ////13,14
		
		if(Account_ID !=0)
			sql.append(" and vv.account_id=?"); ///15
		sql.append(" order by value");
		return sql.toString();
	}
	private String getBpartnerSql(){
		StringBuffer sql = new StringBuffer();
		sql.append("select ww.c_bpartner_id,bp.value,bp.name,"
				+" (CASE WHEN (COALESCE(sum(xx.dr1),0)-COALESCE(sum(xx.cr1),0))<0 THEN 0 ELSE (COALESCE(sum(xx.dr1),0)-COALESCE(sum(xx.cr1),0)) END)as dr1,"
				+" (CASE WHEN (COALESCE(sum(xx.dr1),0)-COALESCE(sum(xx.cr1),0))<0 THEN -1*(COALESCE(sum(xx.dr1),0)-COALESCE(sum(xx.cr1),0)) ELSE 0 END)as cr1,"
				+" COALESCE(sum(xx.dr2),0)as dr2,COALESCE(sum(xx.cr2),0)as cr2,"
				+" (CASE WHEN((COALESCE(sum(xx.dr1),0)+COALESCE(sum(xx.dr2),0))-(COALESCE(sum(xx.cr1),0)+COALESCE(sum(xx.cr2),0)))<0 THEN 0 " 
				+" ELSE ((COALESCE(sum(xx.dr1),0)+COALESCE(sum(xx.dr2),0))-(COALESCE(sum(xx.cr1),0)+COALESCE(sum(xx.cr2),0)))END)as dr3,"
				+" (CASE WHEN((COALESCE(sum(xx.dr1),0)+COALESCE(sum(xx.dr2),0))-(COALESCE(sum(xx.cr1),0)+COALESCE(sum(xx.cr2),0)))<0 " 
				+" THEN -1*((COALESCE(sum(xx.dr1),0)+COALESCE(sum(xx.dr2),0))-(COALESCE(sum(xx.cr1),0)+COALESCE(sum(xx.cr2),0))) ELSE 0 END)as cr3"
				+" from (select c_bpartner_id from adempiere.fact_acct where ad_client_id=? and ad_org_id=? " //1,2
				+" and account_id=? and dateacct<=? and c_bpartner_id is not null group by c_bpartner_id) as ww"    //3,4
				+" left join (select * from ( select c_bpartner_id as bp1,sum(amtacctdr) as dr1,sum(amtacctcr) as cr1 from adempiere.fact_acct"
				+" where ad_client_id=? and ad_org_id=?  and account_id=? and dateacct<? and c_bpartner_id is not null group by c_bpartner_id) as zz" //5,6,7,8
				+" full join (select c_bpartner_id as bp2,sum(amtacctdr) as dr2,sum(amtacctcr) as cr2 from adempiere.fact_acct "
				+" where ad_client_id=? and ad_org_id=? and account_id=? and dateacct between ? and ? and c_bpartner_id is not null group by c_bpartner_id) as yy" //9,10,11,12,13
				+" on(zz.bp1=yy.bp2)) as xx on(ww.c_bpartner_id=xx.bp1 or ww.c_bpartner_id=xx.bp2) left join adempiere.c_bpartner bp on(ww.c_bpartner_id=bp.c_bpartner_id)");
		if(C_BPartner_ID!=0)
			sql.append(" where ww.c_bpartner_id=?"); //14
		sql.append(" group by ww.c_bpartner_id,bp.value,bp.name");
		return sql.toString();
	}
	private String getUserelementSql(){
		StringBuffer sql = new StringBuffer();
		sql.append("select" 
				+" userelement1_id,"
				+" COALESCE(c.description,'without description') as description,"
				+" c.cms_contract_contractnum,"
				+" (CASE WHEN (COALESCE(sum(xx.dr1),0)-COALESCE(sum(xx.cr1),0))<0 THEN 0 ELSE (COALESCE(sum(xx.dr1),0)-COALESCE(sum(xx.cr1),0)) END)as dr1,"
				+" (CASE WHEN (COALESCE(sum(xx.dr1),0)-COALESCE(sum(xx.cr1),0))<0 THEN -1*(COALESCE(sum(xx.dr1),0)-COALESCE(sum(xx.cr1),0)) ELSE 0 END)as cr1,"
				+" COALESCE(sum(xx.dr2),0)as dr2,COALESCE(sum(xx.cr2),0)as cr2,"
				+" (CASE WHEN((COALESCE(sum(xx.dr1),0)+COALESCE(sum(xx.dr2),0))-(COALESCE(sum(xx.cr1),0)+COALESCE(sum(xx.cr2),0)))<0" 
				+" THEN 0 ELSE ((COALESCE(sum(xx.dr1),0)+COALESCE(sum(xx.dr2),0))-(COALESCE(sum(xx.cr1),0)+COALESCE(sum(xx.cr2),0)))END)as dr3,"
				+" (CASE WHEN((COALESCE(sum(xx.dr1),0)+COALESCE(sum(xx.dr2),0))-(COALESCE(sum(xx.cr1),0)+COALESCE(sum(xx.cr2),0)))<0 "
				+" THEN -1*((COALESCE(sum(xx.dr1),0)+COALESCE(sum(xx.dr2),0))-(COALESCE(sum(xx.cr1),0)+COALESCE(sum(xx.cr2),0))) ELSE 0 END)as cr3"
				+" from (select userelement1_id from adempiere.fact_acct where ad_client_id=? and ad_org_id=?  and c_bpartner_id=?" //1,2,3 
				+" and dateacct<=? and account_id=? and userelement1_id is not null group by userelement1_id) as ww " //4,5
				+" left join "
				+" (select * from  (select userelement1_id as ue1,sum(amtacctdr) as dr1,sum(amtacctcr) as cr1 from adempiere.fact_acct" 
				+" where ad_client_id=? and ad_org_id=? and c_bpartner_id=? and dateacct<? " //6,7,8,9
				+" and account_id=? and userelement1_id is not null group by userelement1_id) as zz" //10
				+" full join (select userelement1_id as ue2,sum(amtacctdr) as dr2,sum(amtacctcr) as cr2 from adempiere.fact_acct "
				+" where ad_client_id=? and ad_org_id=? and c_bpartner_id=? and dateacct between ? and ?" //11,12,13,14,15
				+" and account_id=? and userelement1_id is not null group by userelement1_id) as yy on(zz.ue1=yy.ue2)) as xx " //16
				+" on(ww.userelement1_id=xx.ue1 or ww.userelement1_id=xx.ue2)"
				+" left join adempiere.cms_contract c on(ww.userelement1_id=c.cms_contract_id) ");
		if(UserElement1_ID!=0)
			sql.append(" where userelement1_id=?"); //17
		sql.append(" group by userelement1_id,c.description,c.cms_contract_contractnum");
		return sql.toString();
	} 
	public class AccountValue{
		private int ad_client_id;
		private int ad_org_id;
		private int account_id;
		private int c_bpartner_id;
		private double dr1;
		private double cr1;
		private double dr2;
		private double cr2;
		private double dr3;
		private double cr3;
		private String value;
		private String name;
		private String period;
		private int userelement1_id;
		private String description;
		private String cms_contract_contractnum;
		
		
		public String getPeriod() {
			return period;
		}
		public void setPeriod(String period) {
			this.period = period;
		}
		public int getUserelement1_id() {
			return userelement1_id;
		}
		public void setUserelement1_id(int userelement1_id) {
			this.userelement1_id = userelement1_id;
		}
		public String getDescription() {
			return description;
		}
		public void setDescription(String description) {
			this.description = description;
		}
		public String getCms_contract_contractnum() {
			return cms_contract_contractnum;
		}
		public void setCms_contract_contractnum(String cms_contract_contractnum) {
			this.cms_contract_contractnum = cms_contract_contractnum;
		}
		public int getC_bpartner_id() {
			return c_bpartner_id;
		}
		public void setC_bpartner_id(int c_bpartner_id) {
			this.c_bpartner_id = c_bpartner_id;
		}
		public int getAd_client_id() {
			return ad_client_id;
		}
		public void setAd_client_id(int ad_client_id) {
			this.ad_client_id = ad_client_id;
		}
		public int getAd_org_id() {
			return ad_org_id;
		}
		public void setAd_org_id(int ad_org_id) {
			this.ad_org_id = ad_org_id;
		}
		public int getAccount_id() {
			return account_id;
		}
		public void setAccount_id(int account_id) {
			this.account_id = account_id;
		}
		public double getDr1() {
			return dr1;
		}
		public void setDr1(double dr1) {
			this.dr1 = dr1;
		}
		public double getCr1() {
			return cr1;
		}
		public void setCr1(double cr1) {
			this.cr1 = cr1;
		}
		public double getDr2() {
			return dr2;
		}
		public void setDr2(double dr2) {
			this.dr2 = dr2;
		}
		public double getCr2() {
			return cr2;
		}
		public void setCr2(double cr2) {
			this.cr2 = cr2;
		}
		public double getDr3() {
			return dr3;
		}
		public void setDr3(double dr3) {
			this.dr3 = dr3;
		}
		public double getCr3() {
			return cr3;
		}
		public void setCr3(double cr3) {
			this.cr3 = cr3;
		}
		public String getValue() {
			return value;
		}
		public void setValue(String value) {
			this.value = value;
		}
		public String getName() {
			return name;
		}
		public void setName(String name) {
			this.name = name;
		}
		
		
		
	}
}
